/** 
 * File-Name:MySqlDao.java
 *
 * Created on 2010-11-30 下午10:53:57
 * 
 * @author: Neo (neolimeng@gmail.com)
 * Software Engineering Institute, Peking University, China
 * 
 * Copyright (c) 2009, Peking University
 * 
 *
 */
package org.neocrawler.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;
import org.neocrawler.crawler.CrawlerManager;
import org.neocrawler.entity.WebContent;

/**
 * Description:
 * 
 * @author: Neo (neolimeng@gmail.com) Software Engineering Institute, Peking
 *          University, China
 * @version 1.0 2010-11-30 下午10:53:57
 */
public class WebContentDao {
	private static Logger log = Logger.getLogger(WebContentDao.class);
	private static final String DATABASE_URL = CrawlerManager
			.getConfiguration().getDbUrl();
	private static final String USER_NAME = CrawlerManager.getConfiguration()
			.getDbUserName();
	private static final String PASSWORD = CrawlerManager.getConfiguration()
			.getDbPassword();
	private QueryRunner queryRunner;

	public WebContentDao() {
		queryRunner = new QueryRunner();
	}

	public List<WebContent> getAll(Connection connection) {
		String sql = "select * from web_content";
		return find(connection, sql);
	}

	public List<WebContent> getUnvisitedWebContents() {
		String sql = "select * from web_content where status=?";
		return find(sql, WebContent.STATUS_UNKNOWN);
	}

	public List<WebContent> getVisitedWebCotents() {
		String sql = "select * from web_content where status!=?";
		return find(sql, WebContent.STATUS_UNKNOWN);
	}

	public WebContent getWebContent(Connection connection, String url) {
		String sql = "select * from web_content where url=?";
		List<WebContent> webContents = find(connection, sql, url);
		if (webContents != null && webContents.size() > 0) {
			return webContents.get(0);
		} else {
			return null;
		}
	}

	public List<WebContent> getWebContents(String uuid) {
		String sql = "select * from web_content where uuid=?";
		return find(sql, uuid);
	}

	public List<WebContent> getWebContents(Connection connection,
			Integer startCount, Integer pageSize) {
		String sql = "select * from web_content limit ? , ?";
		Object[] params = { startCount, pageSize };
		return find(connection, sql, params);
	}

	public List<WebContent> getWebContentsByStatus(String status) {
		String sql = "select * from web_content where status=?";
		return find(sql, status);
	}

	public List<WebContent> getWebContentsByType(String type) {
		String sql = "select * from web_content where type=?";
		return find(sql, type);
	}

	public List<WebContent> getWebContentsByProperty(String key, String value) {
		String sql = "select * from web_content where " + key + "=?";
		return find(sql, value);
	}

	public Boolean isExist(WebContent webContent) {
		Connection connection = null;
		try {
			connection = getConnection();
			return isExist(connection, webContent);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				DbUtils.close(connection);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public Boolean isExist(Connection connection, WebContent webContent) {
		String sql = "select * from web_content where uuid=? or url=?";
		Object[] params = new Object[] { webContent.getUuid(),
				webContent.getUrl() };
		List<WebContent> webContents = find(connection, sql, params);
		if (webContents != null && webContents.size() > 0) {
			log.debug("存在！" + webContents.size());
			return true;
		} else {
			log.debug("不存在！");
			return false;
		}
	}

	public void save(WebContent webContent) {
		Connection connection = null;
		try {
			connection = getConnection();
			save(connection, webContent);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(connection);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void save(Connection connection, WebContent webContent) {
		log.debug("保存" + webContent.getUrl() + "\t " + webContent.getUuid());
		String sql = "insert into web_content (uuid, url, parentUrl, parentUuid, localStorePath, status, type, exceptionName, exceptionStackTrace, remarks, httpStatusCode, httpStatusLine, reserve01, reserve02, reserve03, reserve04, reserve05, entityDate)"
				+ " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		Object[] params = new Object[] { webContent.getUuid(),
				webContent.getUrl(), webContent.getParentUrl(),
				webContent.getParentUuid(), webContent.getLocalStorePath(),
				webContent.getStatus(), webContent.getType(),
				webContent.getExceptionName(),
				webContent.getExceptionStackTrace(), webContent.getRemarks(),
				webContent.getHttpStatusCode(), webContent.getHttpStatusLine(),
				webContent.getReserve01(), webContent.getReserve02(),
				webContent.getReserve03(), webContent.getReserve04(),
				webContent.getReserve05(), webContent.getEntityDate() };
		execute(connection, sql, params);
	}

	public void saveOrUpdate(WebContent webContent) {
		if (isExist(webContent)) {
			update(webContent);
		} else {
			save(webContent);
		}
	}

	public void saveOrUpdate(Connection connection, WebContent webContent) {
		if (isExist(connection, webContent)) {
			update(connection, webContent);
		} else {
			save(connection, webContent);
		}
	}

	public void saveOrUpdate(List<WebContent> webContents) {
		if (webContents == null) {
			log.error("save(List<WebContent> webContents)传入的参数为空！！");
		} else {
			Connection connection = null;
			try {
				connection = getConnection();
				for (WebContent webContent : webContents) {
					saveOrUpdate(connection, webContent);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					DbUtils.close(connection);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void update(Connection connection, WebContent webContent) {
		log.debug("更新" + webContent.getUrl() + "\t " + webContent.getUuid());
		String sql = "update web_content set url=?, parentUrl=?, parentUuid=?, localStorePath=?, status=?, type=?, exceptionName=?, exceptionStackTrace=?, remarks=?, httpStatusCode=?, httpStatusLine=?, reserve01=?, reserve02=?, reserve03=?, reserve04=?, reserve05=?, entityDate=? where uuid=?";
		Object[] params = new Object[] { webContent.getUrl(),
				webContent.getParentUrl(), webContent.getParentUuid(),
				webContent.getLocalStorePath(), webContent.getStatus(),
				webContent.getType(), webContent.getExceptionName(),
				webContent.getExceptionStackTrace(), webContent.getRemarks(),
				webContent.getHttpStatusCode(), webContent.getHttpStatusLine(),
				webContent.getReserve01(), webContent.getReserve02(),
				webContent.getReserve03(), webContent.getReserve04(),
				webContent.getReserve05(), new Date(), webContent.getUuid() };
		execute(connection, sql, params);
	}

	public void update(WebContent webContent) {
		Connection connection = null;
		try {
			connection = getConnection();
			update(connection, webContent);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(connection);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void setStatusForRecrawl() {
		Connection connection = null;
		try {
			connection = getConnection();
			String sql = "update web_content set status=? where status !=?";
			Object[] params = { WebContent.STATUS_UNKNOWN, WebContent.STATUS_OK };
			execute(sql, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(connection);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public synchronized void execute(String sql, Object... params) {
		Connection connection = null;
		try {
			connection = getConnection();
			execute(connection, sql, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(connection);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public synchronized void execute(Connection connection, String sql,
			Object... params) {
		try {
			queryRunner.update(connection, sql, params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public synchronized List<WebContent> find(String sql, Object... params) {
		Connection connection = null;
		try {
			connection = getConnection();
			return find(connection, sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				DbUtils.close(connection);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public synchronized List<WebContent> find(Connection connection,
			String sql, Object... params) {
		List<WebContent> webContents = null;
		ResultSetHandler<List<WebContent>> handler = new BeanListHandler<WebContent>(
				WebContent.class);
		try {
			webContents = queryRunner.query(connection, sql, handler, params);
			return webContents;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public static synchronized Connection getConnection() {
		Connection connection = null;
		// 加载驱动程序以连接数据库
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(DATABASE_URL, USER_NAME,
					PASSWORD);
			log.debug("WebContentDao getConnection() 取得连接，执行一次！");
		}
		// 捕获加载驱动程序异常
		catch (ClassNotFoundException cnfex) {
			log.error("装载 JDBC/ODBC 驱动程序失败");
			cnfex.printStackTrace();
		}
		// 捕获连接数据库异常
		catch (SQLException sqlex) {
			log.error("无法连接数据库");
			sqlex.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}

	/**
	 * Description:
	 * 
	 * @param args
	 *            void
	 */
	public static void main(String[] args) {
		WebContentDao mySqlDao = new WebContentDao();
		List<WebContent> webContents = mySqlDao.getUnvisitedWebContents();

		System.out.println(webContents.size());

		WebContent webContent = new WebContent();
		webContent.setUuid("59d3e9f3-1966-42de-8e86-2aac279db244");
		webContent.setUrl("url");
		webContent.setExceptionStackTrace("exceptionStackTrace");
		webContent.setLocalStorePath("localStorePath");
		webContent.setRemarks("remarks");
		webContent.setExceptionName("exceptionName");
		webContent.setHttpStatusCode("200");
		webContent.setHttpStatusLine("httpStatusLine");
		webContent.setParentUrl("parent");
		webContent.setReserve01("res09999");
		webContent.setReserve02("res08888");
		webContent.setReserve03("res03");
		webContent.setReserve04("res04");
		webContent.setReserve05("res05");
		webContent.setParentUuid("parentUuid");
		mySqlDao.update(webContent);
	}

}
